/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.util.database;

import com.util.Fecha;
import com.util.files.Ficheros;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import ventasdia.Variables;
import ventasdia.modelo.ArticuloVO;
import ventasdia.modelo.ClienteVO;
import ventasdia.modelo.PedidoVO;

/**
 *
 * @author Usuario
 */
public class UtilidadesBaseDatos {
    
      private static final String SQL_TODOS_ARTICULOS_PEDIDOS = "Select a.Codprov, a.Descripción, a.Stock1, a.Lotmin from artículos as a where a.Stock2>0 ORDER BY FamSub asc, Descripción asc";
    
    public static Connection establecerConexion() throws ClassNotFoundException, SQLException{
           return establecerConexion(Variables.rutaBaseDatosTrabajo);         
    }
    
    
    public static Connection establecerConexion(String ruta)throws ClassNotFoundException, SQLException{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String myDB ="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+ruta;
        
            Properties props = new Properties();
            props.put ("charSet", "iso-8859-1");
            Connection conn = DriverManager.getConnection(myDB, props);
            return conn;
    }
    
    
    public static void cerrarConexion(Connection conn) throws SQLException{
          conn.close();
    }
    
    /**
     * Realiza consulta con un único resultado
     * @param sql 
     */
    public static String realizarConsultastr(String sql){
        String resultado = "";
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion();
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while ( resultados.next() )
                {
                  Object o =resultados.getObject(1);
                  if (o!=null){
                      resultado = o.toString();
                  }
                  else {
                      resultado = "0";
                  }
                  
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            System.out.println(sql);
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
       return resultado;
    }
    
    public static Date realizarConsultadate (String sql){
        Date resultado = new Date();
        Object o = realizarConsulta(sql);
        if (o!=null){
            resultado = (Date)o;
        }

        return resultado;
    }
    
    private static Object realizarConsulta(String sql){
        Object resultado = new Object();
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion();
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while ( resultados.next() )
                {
                  resultado =resultados.getObject(1);
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
       return resultado;
    }
    
    public static List realizarConsultaListaArticuloVO(String sql){
        return realizarConsultaListaArticuloVO(Variables.rutaBaseDatosTrabajo, sql);
    }
    
    public static List<ArticuloVO> realizarConsultaListaArticuloVO(String ruta,String sql){
        
        List<ArticuloVO> listaResultado = new ArrayList<ArticuloVO>();
         
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion(ruta);
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while (resultados.next()){
                  ArticuloVO a = new ArticuloVO();
                  a.codigo = resultados.getString("CODIGO");
                  a.descripcion = resultados.getString("DESCRI");
                  a.loteMinimo = resultados.getInt("LOTMIN");
                  //a.unicaj = resultados.getInt("UNICAJ");
                  a.pvp = resultados.getFloat("PVP");
                  //a.pvpAnt = resultados.getFloat("PVPAnt");
                  listaResultado.add(a);
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
        
        return listaResultado;
    }
    
    public static List realizarConsultaListaStockArticuloVO(String sql){
        return realizarConsultaListaStockArticuloVO(Variables.rutaBaseDatosTrabajo, sql);
    }
    public static List<ArticuloVO> realizarConsultaListaStockArticuloVO(String ruta,String sql){
        
        List<ArticuloVO> listaResultado = new ArrayList<ArticuloVO>();
         
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion(ruta);
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while (resultados.next()){
                  ArticuloVO a = new ArticuloVO();
                  a.codigo = resultados.getString("Codprov");
                  a.descripcion = resultados.getString("Descripción");
                  a.stock = resultados.getFloat("Stock1");
                  listaResultado.add(a);
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
        
        return listaResultado;
    }
    
     public static List<ArticuloVO> obtenerTodosArticuloVO(String ruta,String sql){
        
        List<ArticuloVO> listaResultado = new ArrayList<ArticuloVO>();
         
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion(ruta);
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while (resultados.next()){
                  ArticuloVO a = new ArticuloVO();
                  a.codigo = resultados.getString("art");
                  a.descripcion = resultados.getString("descri");
                  listaResultado.add(a);
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
        
        return listaResultado;
    }
    
    public static void actualizar(String sql){
        try {
                java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion(Variables.rutaBaseDatosTrabajo);
                java.sql.Statement st = conn.createStatement();
                st.executeUpdate(sql);
                
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UtilidadesBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UtilidadesBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

   
    public static List realizarConsultaListaClienteVO(){
        return realizarConsultaListaClienteVO("select * from Clientes where Tcomp>0 and NPerVR <>'' and Habitual='S'  order by Tcomp desc ",Variables.rutaBaseDatosTrabajo);
    }
    
    
    
    public static List realizarConsultaListaClienteVO(String sql,String ruta){
        List<ClienteVO> listaResultado = new ArrayList<ClienteVO>();
         
          try {
            if(Variables.respaldo){
                Ficheros.mover(Variables.rutaBitronik, Variables.rutaRespaldo);
            }
            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion(ruta);
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(sql); 
           if (resultados!=null){
            while (resultados.next()){
                  ClienteVO c = new ClienteVO();
                  c.codigoCliente = resultados.getString("Cliente");
                  c.nombreCliente = resultados.getString("Nombre");
                  c.identificador = resultados.getString("Identifica");
                  c.fechaCaducidad = new Fecha(resultados.getDate("FCadVR"));
                  c.totalCompra = resultados.getFloat("Tcomp");
                  c.ultimaCompra = new Fecha(resultados.getDate("Fultcom"));
                  
                  listaResultado.add(c);
                }
           }
           UtilidadesBaseDatos.cerrarConexion(conn);
        } catch (SQLException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("Utilidades base de datos --> Realizar consulta").log(Level.SEVERE, null, ex);
        }
        
        return listaResultado;
    }
    
    
    
    
    
        public static List<PedidoVO> obtenerTodosArticulosPedidoVO() {
        List<PedidoVO> listaPedido = new ArrayList<PedidoVO>();
        try {

            java.sql.Connection conn = UtilidadesBaseDatos.establecerConexion();
            java.sql.Statement select = conn.createStatement();
            ResultSet resultados = select.executeQuery(SQL_TODOS_ARTICULOS_PEDIDOS);

            if (resultados != null) {
                while (resultados.next()) {
                    PedidoVO p = new PedidoVO();
                    p.setCodigo(resultados.getString("Codprov"));
                    p.setDescripcion(resultados.getString("Descripción"));
                    p.setStock(resultados.getFloat("Stock1"));
                    p.setMediames(calculaMediaMes(p.getCodigo()));
                    p.setPedir(calculaPedir(p.getMediames(),p.getStock(),resultados.getFloat("Lotmin")));
                    
                    
                    listaPedido.add(p);
                }
            }

            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UtilidadesBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UtilidadesBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listaPedido;
    }
    
    private static Float calculaPedir(Float mediames,Float stock, Float lotemin){
        Float necesidad = (mediames * 12)- stock;
        
        if(necesidad.intValue() > lotemin){
            while(necesidad.intValue() % lotemin !=0){
                necesidad++;
            }
            necesidad = new Float (necesidad.intValue());
        }else{
            if (necesidad>0){
                necesidad = lotemin;
            }
            else{
                necesidad=0f;
            }
        }
        
        return necesidad;
    }
    
    
      private static Float calculaMediaMes(String cod_bus) {
        Fecha fechaActual = new Fecha();
        String sql;
        sql = "Select distinct(a.Artículo) from artículos a left join asignaciones n on a.Artículo = n.Artículo where a.Código2='" + cod_bus + "' or a.Codprov = '" + cod_bus + "' or a.Artículo = '" + cod_bus + "' or n.Codasig ='" + cod_bus + "'";
        String codigo = UtilidadesBaseDatos.realizarConsultastr(sql);
        
        sql = "Select sum(v.Cdad) from Ventas v where v.Artículo = '" + codigo + "' and Fecha Between (DateSerial(" + fechaActual.ano + "," + fechaActual.mes + ",1)+TimeSerial(0,0,0)) And  (DateSerial(" + fechaActual.ano + "," + fechaActual.mes + "," + fechaActual.dia + ")+TimeSerial(23,59,59))";
        Float vmes = new Float (realizarConsultastr(sql));
        return (vmes / fechaActual.dia);
    }

}
